package com.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang.ArrayUtils;
import org.apache.log4j.Logger;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBUtils {
	private static Logger logger = Logger.getLogger(DBUtils.class);
	private static SystemProUtils pro = SystemProUtils.getInstance();
	
	public static ComboPooledDataSource dataSource;  
	static {  
	    try {  
	        dataSource = new ComboPooledDataSource();  
	        dataSource.setUser(pro.getProperty("jdbc.user.local.mysql"));  
	        dataSource.setPassword(pro.getProperty("jdbc.pwd.local.mysql"));  
	        dataSource.setJdbcUrl(pro.getProperty("jdbc.url.local.mysql"));  
	        dataSource.setDriverClass(pro.getProperty("jdbc.class.local.mysql"));  
	        dataSource.setInitialPoolSize(parseInt(pro.getProperty("jdbc.pool.initialPoolSize"),50));  
	        dataSource.setMinPoolSize(parseInt(pro.getProperty("jdbc.pool.minPoolSize"),60));  
	        dataSource.setMaxPoolSize(parseInt(pro.getProperty("jdbc.pool.maxPoolSize"),500));  
	        dataSource.setAcquireIncrement(parseInt(pro.getProperty("jdbc.pool.acquireIncrement"),3));
	        dataSource.setMaxStatements(parseInt(pro.getProperty("jdbc.pool.maxStatements"),100));
	        dataSource.setIdleConnectionTestPeriod(parseInt(pro.getProperty("jdbc.pool.idleConnectionTestPeriod"),18000));
	        dataSource.setAcquireRetryAttempts(parseInt(pro.getProperty("jdbc.pool.acquireRetryAttempts"),50));
	        dataSource.setCheckoutTimeout(parseInt(pro.getProperty("jdbc.pool.checkoutTimeout"), 100));
	        dataSource.setMaxIdleTime(parseInt(pro.getProperty("jdbc.pool.maxIdleTime"),25000));  
	        dataSource.setAutoCommitOnClose(true);
	        dataSource.setTestConnectionOnCheckin(parseBoolean(pro.getProperty("jdbc.pool.testConnectionOnCheckin"),true));
	        dataSource.setTestConnectionOnCheckout(parseBoolean(pro.getProperty("jdbc.pool.testConnectionOnCheckout"),true));
	        dataSource.setAutomaticTestTable(pro.getProperty("jdbc.pool.autoTestTable"));
	        dataSource.setUnreturnedConnectionTimeout(parseInt(pro.getProperty("jdbc.pool.unreturnedConnectionTimeout"),180));
	        dataSource.setMaxConnectionAge(parseInt(pro.getProperty("jdbc.pool.unreturnedConnectionTimeout"),180));
	        dataSource.setMaxIdleTimeExcessConnections(parseInt(pro.getProperty("jdbc.pool.unreturnedConnectionTimeout"),180));
	        
	    } catch (Exception e) {  
	        e.printStackTrace();  
	    }  
	}  
	/** 
	* @Title: parseInt 
	* @Description: TODO(强转int) 
	* @param @return    设定文件 
	* @return int    返回类型 
	*/
	private static int parseInt(String source,int def){
		try {
			return Integer.parseInt(source);
		} catch (Exception e) {
			return def;
		}
	}
	
	/** 
	* @Title: parseBoolea 
	* @Description: TODO(强转int) 
	* @param @return    设定文件 
	* @return int    返回类型 
	*/
	private static Boolean parseBoolean(String source,Boolean def){
		try {
			return Boolean.valueOf(source);
		} catch (Exception e) {
			return def;
		}
	}
	public static Connection getKingBaseConnection() throws Exception{
		Connection conn=null;
		Class.forName("com.kingbase.Driver");
		conn = DriverManager.getConnection("jdbc:kingbase://192.168.20.1:54321/XNY", "HISDB", "HISDB");
		return conn;
	}
	/**
	 * @throws SQLException  
	* @Title: getConnection 
	* @Description: TODO(获取默认数据源) 
	* @param @return    设定文件 
	* @return Connection    返回类型 
	* @throws 
	*/
	public synchronized static Connection getConnection() throws SQLException{
		return dataSource.getConnection();
		
	}
	
	public static Connection getConnection(String param) throws Exception{
		if(param!=null && param.length() >0){
			Connection conn=null;
			Class.forName(pro.getProperty("jdbc.class"+param));
			conn = DriverManager.getConnection(pro.getProperty("jdbc.url"+param),pro.getProperty("jdbc.user"+param),pro.getProperty("jdbc.pwd"+param));
			return conn;
		}else{
			logger.info("创建数据库连接失败,请检查参数");
			return null;
		}
	}
	
	 /**查询多条记录 
     * @param sql 
     * @param params 
     * @return 
     * @throws SQLException 
     */  
    public static List<Map<String, Object>> findModeResult(String sql,Connection conn,List<Object> params) throws SQLException{  
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); 
		int index = 1;
		PreparedStatement ps = null;
		ResultSet resultSet = null;
		try {
			ps = conn.prepareStatement(sql);
			if (params != null && !params.isEmpty()) {
				for (int i = 0; i < params.size(); i++) {
					ps.setObject(index++, params.get(i));
				}
			}
			resultSet = ps.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int cols_len = metaData.getColumnCount();
			while (resultSet.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < cols_len; i++) {
					String cols_name = metaData.getColumnLabel(i + 1);
					Object cols_value = resultSet.getObject(cols_name);
					if (cols_value == null) {
						cols_value = "";
					}
					map.put(cols_name, cols_value);
				}
				list.add(map);
			}
		} catch (SQLException ex) {
			throw ex;
		} finally {
			closeConn(resultSet, ps, null);
		}

		return list;
    } 
    
    /**查询多条记录 
     * @param sql 
     * @param params 
     * @param falg 是否关闭 conn 
     * @return 
     * @throws SQLException 
     */  
    public static List<Map<String, Object>> findModeResult(String sql,Connection conn,List<Object> params,boolean falg) throws SQLException{  
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		int index = 1;
		ResultSet resultSet = null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			if (params != null && !params.isEmpty()) {
				for (int i = 0; i < params.size(); i++) {
					ps.setObject(index++, params.get(i));
				}
			}
			resultSet = ps.executeQuery();
			ResultSetMetaData metaData = resultSet.getMetaData();
			int cols_len = metaData.getColumnCount();
			while (resultSet.next()) {
				Map<String, Object> map = new HashMap<String, Object>();
				for (int i = 0; i < cols_len; i++) {
					String cols_name = metaData.getColumnLabel(i + 1);
					Object cols_value = resultSet.getObject(cols_name);
					if (cols_value == null) {
						cols_value = "";
					}
					map.put(cols_name, cols_value);
				}
				list.add(map);
			}
		} catch (SQLException ex) {
			throw ex;
		} finally {
			if (falg) {
				closeConn(resultSet, ps, conn);
			} else {
				closeConn(resultSet, ps, null);
			}
		}
		return list;
    } 
    
    /** 
    * @Title: selectMapByKeyValueCol 
    * @Description: TODO(根据key 和 value列 返回map) 
    * @param conn
    * @param sql
    * @param params
    * @param key
    * @param value
    * @return Map<String,Object>
     * @throws SQLException 
    */
    public static Map<String, Object> selectMapByKeyValueCol(Connection conn,String sql,List<Object> params,String key,String value) throws SQLException{
    	ResultSet resultSet = null;
    	PreparedStatement ps = null;
    	Map<String, Object> resultmMap = null;
    	try{
    		resultmMap = new HashMap<String, Object>();
        	ps = conn.prepareStatement(sql);  
        	int index = 1;
        	if(params != null && !params.isEmpty()){  
        		for(int i = 0; i<params.size(); i++){  
        			ps.setObject(index++, params.get(i));  
        		}  
        	}  
        	resultSet = ps.executeQuery();
        	while(resultSet.next()){
        		resultmMap.put(resultSet.getString(key),resultSet.getObject(value));
        	}
    	}catch(SQLException ex){
    		throw ex;
    	}finally{
    		closeConn(resultSet, ps, null);
    	}
    	
    	
    	return resultmMap;
    }
    /** 查询多条记录 
     * @param sql 
     * @param params 
     * @return 
     * @throws SQLException 
     */  
    public static List<Map<String, String>> findModeResult(Connection conn,String sql,List<Object> params) throws SQLException{  
    	ResultSet resultSet = null;
    	PreparedStatement ps = null;
    	List<Map<String, String>> list = null;
    	try{
    		list = new ArrayList<Map<String, String>>(); 
        	int index = 1;  
        	ps = conn.prepareStatement(sql);  
        	if(params != null && !params.isEmpty()){  
        		for(int i = 0; i<params.size(); i++){  
        			ps.setObject(index++, params.get(i));  
        		}  
        	}  
        	resultSet = ps.executeQuery();  
        	ResultSetMetaData metaData = resultSet.getMetaData();  
        	int cols_len = metaData.getColumnCount();  
        	while(resultSet.next()){
        		Map<String, String> map = new HashMap<String, String>();  
        		for(int i=0; i<cols_len; i++){  
        			String cols_name = metaData.getColumnLabel(i+1);  
        			String cols_value = resultSet.getString(cols_name);  
        			if(cols_value == null){  
        				cols_value = "";  
        			}  
        			map.put(cols_name, cols_value);  
        		}  
        		list.add(map); 
        	} 
    	}catch(SQLException ex){
    		throw ex;
    	}finally{
    		closeConn(resultSet, ps, null);
    	}
    	
    	
    	return list;  
    }  
    
	public static void closeConn(ResultSet rs,Statement st,Connection conn) throws SQLException{
		if(rs!=null){
			rs.close();
			rs=null;
		}
		if(st!=null){
			st.close();
			st=null;
		}
		if(conn!=null){
			conn.close();
			conn=null;
		}
	}
	
	public static String genteUUID(){
		String uuid = UUID.randomUUID().toString();
		return uuid.replaceAll("-", "");
	}
	/** 
	* @Title: batchSql 
	* @Description: TODO(批量执行) 
	* @param sql 插入sql 
	* @param deleteSql 删除sql 可以为null
	* @param obj 插入的集合
	* @param conn 数据库连接
	* @return void    返回类型 
	*/
	public static void batchSql(String sql,String deleteSql,List<Object[]> obj,Connection conn){
		batchSql(sql,deleteSql,obj,conn,true);
	}
	/** 
	* @Title: batchSql 
	* @Description: TODO(批量执行) 
	* @param sql 插入sql 
	* @param deleteSql 删除sql 可以为null
	* @param obj 插入的集合
	* @param conn 数据库连接
	* @param isClosed 是否关闭 true 是  false 否
	* @return void    返回类型 
	*/
	public static void batchSql(String sql,String deleteSql,List<Object[]> obj,Connection conn,boolean isClosed){
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(false);
			if(deleteSql!=null && deleteSql.length()>0){
				conn.prepareStatement(deleteSql).execute();
				conn.commit();
			}
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < obj.size(); i++) {
				for (int j = 0; j < obj.get(i).length; j++) {
					ps.setObject(j+1, obj.get(i)[j]);
				}
				ps.addBatch();
				if(i%500==0){
					ps.executeBatch();
					conn.commit();
				}
			}
			ps.executeBatch();
			conn.commit();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try {
				if(isClosed){
					DBUtils.closeConn(null, ps, conn);
				}else{
					DBUtils.closeConn(null, ps, null);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	/** 
	* @Title: batchSql 
	* @Description: TODO(批量执行sql语句) 
	* @param @param sqls
	* @param @param deleteSql
	* @param @param conn
	* @param @throws Exception    设定文件 
	* @return void    返回类型 
	*/
	public static int[] batchSql(List<String> sqls,String deleteSql,Connection conn) throws Exception {
		
		Statement st = null;
		int[] result = new int[]{};
		try{
			
			conn.setAutoCommit(false);
			if(deleteSql!=null && deleteSql.length()>0){
				conn.prepareStatement(deleteSql).execute();
				conn.commit();
			}
			st = conn.createStatement();
			for (int i = 0; i < sqls.size(); i++) {
				st.addBatch(sqls.get(i));
				if(i%500==0){
					result = ArrayUtils.addAll(st.executeBatch(),result);
					conn.commit();
				}
			}
			result = ArrayUtils.addAll(st.executeBatch(),result);
			conn.commit();
		}catch(Exception ex){
			throw ex;
		}finally{
			DBUtils.closeConn(null, st, conn);
		}
		return result;
	}
	/** 
	 * @Title: batchSql 
	 * @Description: TODO(批量执行sql语句) 
	 * @param @param sqls
	 * @param @param deleteSql
	 * @param @param conn
	 * @param @param isClose 是否关闭连接
	 * @param @throws Exception    设定文件 
	 * @return void    返回类型 
	 */
	public static int[] batchSql(List<String> sqls,String deleteSql,Connection conn,boolean isClose) throws Exception{
		int[] result = new int[]{};
		Statement st = null;
		try{
		conn.setAutoCommit(false);
		if(deleteSql!=null && deleteSql.length()>0){
			conn.prepareStatement(deleteSql).execute();
			conn.commit();
		}
		st = conn.createStatement();
		for (int i = 0; i < sqls.size(); i++) {
			st.addBatch(sqls.get(i));
			if(i%500==0){
				result = ArrayUtils.addAll(st.executeBatch(),result);
				conn.commit();
			}
		}
		result = ArrayUtils.addAll(st.executeBatch(),result);
		conn.commit();
		}catch(Exception ex){
			throw ex;
		}finally{
			if(isClose){
				DBUtils.closeConn(null, st, conn);
			}else{
				DBUtils.closeConn(null, st, null);
			}
		}
		
		return result;
	}
	
	/**
	 * 功能描述:公共批处理函数
	 * 创建日期：2016-2-26 09:39:06
	 * 作    者： swp
	 * 返 回 值：int[]
	 */
	public static int[] bath(Connection conn,String[] sqls) throws SQLException {
		//关闭自动提交
		conn.setAutoCommit(false);
		Statement stmt = null;
		int[] tiaos = null;
		try {
			//开辟缓冲区---不自动提交
			conn.setAutoCommit(false);
			
			stmt = conn.createStatement();
			//设置超时时间 默认一分钟
			//stmt.setQueryTimeout(60);
			
			for (int i = 0; i < sqls.length; i++) {
				
			
				if(sqls[i]!=null&&!sqls[i].trim().equals("")){
					stmt.addBatch(sqls[i]);
				}
				if(i%100==0){
					stmt.executeBatch();
					conn.commit();
				}
			}
			tiaos = stmt.executeBatch();
			DBUtils.commit(conn);
		} catch (SQLException e) {
			e.printStackTrace();
			DBUtils.rollback(conn);
			try {
				throw new SQLException("批处理异常");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			DBUtils.closeConn(null, stmt, conn);
		}
		return tiaos;
	}
	
	/**
	 * 功能描述:事务提交
	 * 创建日期：2016-2-26 09:49:23
	 * 作    者： swp
	 */
	public static void commit(Connection conn) throws SQLException {
		 try {
            if (conn != null && !conn.getAutoCommit()) {
                conn.commit();
                //conn.setAutoCommit(true);
            } else {
                if (conn == null) {
                    throw new SQLException("connection not opened!");
                } else {
                    throw new SQLException("first begin then commit please!");
                }
            }
        } catch (SQLException e) {
            throw new SQLException("can not commit transaction!", e);
        }
    }
	
	/**
	 * 功能描述:事务回滚
	 * 创建日期：2016-2-26 09:49:51
	 * 作    者： swp
	 */
	public static void rollback(Connection conn) throws SQLException {
        try {
            if (conn != null && !conn.getAutoCommit()) {
                conn.rollback();
                conn.setAutoCommit(true);
            } else {
                if (conn == null) {
                    throw new SQLException("connection not opened!");
                } else {
                    throw new SQLException("first begin then rollback please!");
                }
            }
        } catch (SQLException e) {
            throw new SQLException("can not rollback transaction!", e);
        }
	}
	
	/**
	 * 功能描述:公共处理增删改函数
	 * 创建日期：2016-2-26 10:40:53
	 * 作    者：swp
	 * 返 回 值：int
	 */
	public static int execute(Connection conn,String sql) {
		PreparedStatement ps = null;
		int tiao = 0;
        try {
        	ps = conn.prepareStatement(sql);
        	ps.execute();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				DBUtils.rollback(conn);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			try {
				DBUtils.closeConn(null, ps, conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tiao;
	}
}
